import { Service } from 'egg';
import * as blue from 'bluebird';
import * as _ from 'lodash';
import * as Excel from 'exceljs';
import { ReadPreference } from 'mongodb';
const DB_ACCOUNT_PRODUCT = 'platform_account_product';
// const DB_ACCOUNT = 'platform_account';
const DB_CALL = '_c5_call_sheet';

// let reportCon = {};
// let reportConTemp = {};

// let reportLen = {};
// let reportLenTemp = {};

// let reportHour = {};
// let reportMonth = {};

let time1;

type CALL_DOC = {
  _id: string;
  OFFERING_TIME: string;
  CALL_TIME_LENGTH: number;
  CALLED_NO: string;
  STATUS: string;
  datadb: string;
  account: string;
}

// const TypeStatus = {
//   dealing: 'dealing'
// }

const TypeScope = {
  t10: 't10',
  t20: 't20',
  t30: 't30',
  t40: 't40',
  t50: 't50',
  t60: 't60',
  tmax: 'tmax',
  tno: 'tno',
}

interface ReportType {
  account?: string;
  reportCon: any;
  reportLen: any;
  reportHour: any;
  reportMonth: any;
  reportConTemp?: any;
  reportLenTemp?: any;
}

// const allAns: ReportType[] = [];

export default class SjCallService extends Service {
  
  /**
   * 2020年09月24日20:43:03 审计需求
   */
  public async sjcall() {
    time1 = new Date().getTime();

    const body = this.ctx.request.query;
    // const {startTime, endTime} = body;
    const isoffline = body.isoffline;

    const concurrency = parseInt(body.concurrency) || 1;

    this.ctx.logger.info('concurrency_log: ', concurrency);

    const query = {
      OFFERING_TIME: {
        $gte: '2020-07-01 00:00:00',
        $lte: '2020-10-01 00:00:00',
      },
      // CONNECT_TYPE: {$in: ['dialout', 'dialTransfer']},
      CONNECT_TYPE: 'dialout',
      // STATUS: 'dealing',
    };
    this.ctx.logger.info('统计时间：', query);

    const accounts = await this.getAccounts();
    const all = accounts.length;

    let current = 0;
    // accounts = [{account: 'N00000001674', dataDB: 'cc_data_20161129'}];

    const findlist =  await this.app.commonDB.db('cc').collection('sj_2call_v2').find({}, {fields: {_id: 1, account: 1}}).toArray();
    const findlist2 =  await this.app.commonDB.db('cc').collection('sj_2call_no_sheet_v2').find({}, {fields: {_id: 1}}).toArray();
    const list3 = [...findlist, ...findlist2].map((e) => {
      if (e.account) {
        return e.account;
      }
      return e._id;
    });
    
    await blue.map(accounts, async(accountDoc) => {
      const accountId = accountDoc.account;
      const datadb = accountDoc.dataDB;
      
      if (list3.indexOf(accountId) > -1) {
        current++;
        this.printRate(all, current);
        return;
      }
      
      const reportCon = {};
      const reportLen = {};
      const reportHour = {};
      const reportMonth = {};
      const reportConTemp = {};
      const reportLenTemp = {};

      const reportAll = {
        reportCon,
        reportLen,
        reportHour,
        reportMonth,
        reportConTemp,
        reportLenTemp,
      }

      this.app.logger.info('do_coun=' + accountId );

      let oneAll;

      if (accountId === 'N00000015352') {
        oneAll = 100;
      } else {
        // oneAll = 100;
        oneAll = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_CALL}`).count();
      }
      
      this.app.logger.info('do_count_res==' + accountId + ' ' + oneAll);
      if (oneAll === 0) {
        current++;
        await this.app.commonDB.db('cc').collection('sj_2call_no_sheet_v2')
          .findOneAndUpdate({_id: accountId}, {$set: { data: {}, type: 'no'}}, {upsert: true});
        this.printRate(all, current);
        return;
      }
      let oneCurrent = 0;

      let cursor;
      if (isoffline) {
        this.app.logger.info('offline_yes', accountId);

        // const accountNoList = [
        //   'N00000011517',   // N00000011517=cc_data_20161223===rate==40104962
        //   'N00000003757',   // N00000003757=cc_data_20151204===rate==46481522
        // ];
        // if(accountNoList.includes(accountId)) {
        //   current++;
        //   return;
        // }
        cursor = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_CALL}`).find(query, {fields: {
          OFFERING_TIME: 1,
          CALL_TIME_LENGTH: 1,
          CALLED_NO: 1,
          STATUS: 1,
          _id: 0,
        }}).setReadPreference(new ReadPreference('secondary',[{'offline': 'true'}])).batchSize(10000);
      } else {
        this.app.logger.info('offline_no', accountId);
        cursor = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_CALL}`).find(query, {fields: {
          OFFERING_TIME: 1,
          CALL_TIME_LENGTH: 1,
          CALLED_NO: 1,
          STATUS: 1,
        }}).setReadPreference(ReadPreference.SECONDARY_PREFERRED).batchSize(10000);
      };
      
      let page = 1;
      let doc: CALL_DOC;
      let hasDoc = false;
      while (await cursor.hasNext()) {
        hasDoc = true;
        doc = await cursor.next();
        // this.ctx.logger.info('doc', doc);
        doc.datadb = datadb;
        doc.account = accountId;

        this.processOneCall(doc, reportAll);
        oneCurrent++;
        this.printOneRate(oneAll, oneCurrent, accountId, datadb, doc, page);
        if(oneCurrent % 10000000 === 0) {
          await this.processOneAccountEnd(accountId, reportAll, page);
          this.clearReportInMemory(reportAll);
          this.app.logger.info('clear_memory_ok', reportAll);
          hasDoc = false;
          page++;
        }
      }
      if (hasDoc) {
        await this.processOneAccountEnd(accountId, reportAll);
      } else {
        await this.app.commonDB.db('cc').collection('sj_2call_no_sheet_v2')
          .findOneAndUpdate({_id: accountId}, {$set: { data: {}, type: 'no'}}, {upsert: true});
      }
      
      current++;
      this.printRate(all, current);

    }, {concurrency});

    await this.sumAllAccount();
    this.ctx.sendSuccess();
  }

  public clearReportInMemory(reportAll) {
    reportAll.reportCon = {};
    reportAll.reportLen = {};
    reportAll.reportHour = {};
    reportAll.reportMonth = {};
    reportAll.reportConTemp = {};
    reportAll.reportLenTemp = {};
  }

  public async printRate(all: number, current: number) {
    if (current % 1 === 0) {
      this.app.logger.info('======account==rate======' + `${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async printOneRate(all: number, current: number, accountId: string, datadb: string, doc: CALL_DOC, page: number) {
    if (current % 50000 === 0) {
      this.app.logger.info(`======${accountId}=${datadb}===rate==${doc.OFFERING_TIME} ${page} ${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async processOneAccountEnd(accountId: string, reportall: ReportType, page?: number) {

    const {reportConTemp, reportLenTemp, reportCon, reportLen} = reportall;

    // this.logger.info('acount', account);
    // 处理report1
    Object.keys(reportConTemp).forEach((month) => {
      const dis = reportConTemp[month];
      if (!reportCon[month]) {
        reportCon[month] = {};
      }
      const one = reportCon[month];

      Object.keys(dis).forEach(num => {
        const count = dis[num];
        if (!one[count]) {
          one[count] = 0;
        }
        one[count]++;
      });
    });
    // process report len
    Object.keys(reportLenTemp).forEach((month) => {
      if (!reportLen[month]) {
        reportLen[month] = {};
      }
      const one = reportLen[month];

      const dis = reportLenTemp[month];

      Object.keys(dis).forEach(num => {
        const len_s = dis[num];
        const type = this.getCallScope(len_s);
        if (!one[type]) {
          one[type] = 0;
        }
        one[type]++;
      });
    });
    // TODO: save to db 
    // allAns.push(reportall);
    delete reportall.reportConTemp;
    delete reportall.reportLenTemp;

    reportall.account = accountId;
    const key = page ? `${accountId}_${page}` : accountId; 

    await this.app.commonDB.db('cc').collection('sj_2call')
      .findOneAndUpdate({_id: key}, {$set: { data: reportall, account: accountId}}, {upsert: true});

  }

  public getCallScope(len_s: number) {
    if (typeof len_s !== 'number') {
      return TypeScope.tno;;
    }
    if (len_s <= 10 * 60) {
      return TypeScope.t10;
    } else if (len_s <= 20 * 60) {
      return TypeScope.t20;
    } else if (len_s <= 30 * 60) {
      return TypeScope.t30;
    } else if (len_s <= 40 * 60) {
      return TypeScope.t40;
    } else if (len_s <= 50 * 60) {
      return TypeScope.t50;
    } else if (len_s <= 60 * 60) {
      return TypeScope.t60;
    } else {
      return TypeScope.tmax;
    }
  }

  public processOneCall(doc: CALL_DOC, reportall: ReportType) {
    const {reportConTemp, reportLenTemp, reportHour, reportMonth} = reportall;
    // 1.DA5 手机号被呼叫集中度分析
    const reportConcentrate = (month: string, num: string) => {
      if(num.includes('.')) {
        return ;
      }
      if (!reportConTemp[month]) {
        reportConTemp[month] = {};
      }
      if (!reportConTemp[month][num]) {
        reportConTemp[month][num] = 0;
      }
      reportConTemp[month][num]++;
    }

    // 2. DA7 单个手机呼叫时长集中度分析
    const reportConcentrateLen = (month: string, num: string, len_s: number) =>{
      if(num.includes('.')) {
        return ;
      }
      if (!reportLenTemp[month]) {
        reportLenTemp[month] = {};
      }
      if (!reportLenTemp[month][num]) {
        reportLenTemp[month][num] = 0;
      }
      
      reportLenTemp[month][num] += len_s;
    }
    //3. DA6 呼叫时间分布分析
    const reportHourFunc = (hour: string) => {
      if (!reportHour[hour]) {
        reportHour[hour] = 0;
      }
      reportHour[hour]++;
    }

    // 4.DA8 呼叫时长趋势分析
    const reportMonthFunc = (month: string, len_s: number, doc: CALL_DOC) => {
      if (!reportMonth[month]) {
        reportMonth[month] = 0;
      }
      if (typeof len_s === 'string') {
        try {
          len_s = parseInt(len_s);
        } catch (error) {}
      }
      if (typeof len_s !== 'number') {
        this.app.logger.error('lens_invalid: ', len_s, doc.account, doc.datadb, doc.OFFERING_TIME, doc._id);
        // throw new Error('error');
        return
      }
      reportMonth[month] += len_s;
    }

    const month = this.getMonthFromTime(doc.OFFERING_TIME);
    const hour = this.getHourFromTime(doc.OFFERING_TIME);

    const num = doc.CALLED_NO;
    if (num.includes('.')) {
      return ;
    }
    // 所有
    reportConcentrate(month, doc.CALLED_NO);
    // dealing
    if (doc.STATUS === 'dealing') {
      reportConcentrateLen(month, doc.CALLED_NO, doc.CALL_TIME_LENGTH);
    }
    // 所有
    reportHourFunc(hour);
    // dealing
    if (doc.STATUS === 'dealing') {
      reportMonthFunc(month, doc.CALL_TIME_LENGTH, doc);
    }
  }

  // "2020-08-18 16:02:26" => "202008"
  public getMonthFromTime(time: string): string {
    return time.substr(0, 4) + time.substr(5, 2);
  }

  // "2020-08-18 16:02:26" => "16"
  public getHourFromTime(time: string): string {
    return time.substr(11,2);
  }

  public async sumAllAccount() {
    const sum: ReportType = {
      reportCon: {},
      reportLen: {},
      reportHour: {},
      reportMonth: {},
    };
    
    const list = await this.app.commonDB.db('cc').collection('sj_2call').find().toArray();
    list.forEach(one => {
      one = one.data;
      Object.keys(one.reportCon).forEach((month) => {
        if (!sum.reportCon[month]) {
          sum.reportCon[month] = {};
        }
        const obj = sum.reportCon[month];
        const current = one.reportCon[month];

        Object.keys(current).forEach((num) => {
          if (!obj[num]) {
            obj[num] = 0;
          }
          obj[num] += current[num];
        })
      });

      Object.keys(one.reportLen).forEach((month) => {
        if (!sum.reportLen[month]) {
          sum.reportLen[month] = {};
        }
        const obj = sum.reportLen[month];
        const current = one.reportLen[month];

        Object.keys(current).forEach((type) => {
          if (!obj[type]) {
            obj[type] = 0;
          }
          obj[type] += current[type];
        })
      });

      Object.keys(one.reportHour).forEach((k) => {
        if (!sum.reportHour[k]) {
          sum.reportHour[k] = 0;
        }
        sum.reportHour[k] = sum.reportHour[k] + one.reportHour[k];
      });

      Object.keys(one.reportMonth).forEach((k) => {
        if (!sum.reportMonth[k]) {
          sum.reportMonth[k] = 0;
        }
        sum.reportMonth[k] = sum.reportMonth[k] + one.reportMonth[k];
      });
    });

    this.processAllEnd(sum);
  }

  public async processAllEnd(reportall: ReportType) {

    const {reportHour, reportMonth, reportCon, reportLen} = reportall;



    const filename = `./logs/sj-call-${Date.now()}.xlsx`;
    const options = {
      filename,
      useStyles: true,
      useSharedStrings: true
    };
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    // const workbook = new Excel.Workbook(options);

    // sheet1
    const sheet = workbook.addWorksheet('DA5 手机号被呼叫集中度分析');
    sheet.columns = [
      { key: 'month', header: '月份' } as any,
      { key: 'count', header: '单手机号被呼叫数量' } as any,
      { key: 'phone_count', header: '手机号数量' } as any,
    ];
    let months = Object.keys(reportCon);
    months = months.sort((a, b) => {
      return a > b ? 1: -1
    });
    months.forEach((month) => {
      const one = reportCon[month];
      let list = Object.keys(one);
      list = list.sort((a, b) => {
        return parseInt(a) < parseInt(b) ? 1: -1
      });
      list.forEach((count) => {
        const obj = {
          month,
          count,
          phone_count: one[count],
        };
        sheet.addRow(obj).commit();;
      })
    });
    sheet.commit();

    // sheet2
    const sheet2 = workbook.addWorksheet('DA6 呼叫时间分布分析');
    sheet2.columns = [
      { key: 'time', header: '时间段' } as any,
      { key: 'count', header: '外呼通话数' } as any,
    ];
    let houres = Object.keys(reportHour);
    houres = houres.sort((a, b) => {
      return a > b ? 1: -1
    });
    houres.forEach((hour) => {
      sheet2.addRow({
        time: hour,
        count: reportHour[hour],
      }).commit();
    });
    sheet2.commit();

    // sheet3
    const sheet3 = workbook.addWorksheet('DA8 呼叫时长趋势分析');
    sheet3.columns = [
      { key: 'time', header: '月份' } as any,
      { key: 'count', header: '呼叫总时长(min)' } as any,
    ];
    let monthList = Object.keys(reportMonth);
    monthList = monthList.sort((a, b) => {
      return a > b ? 1: -1
    });
    monthList.forEach((month) => {
      sheet3.addRow({
        time: month,
        count: Math.round(reportMonth[month]/60),
      }).commit();
    });
    this.app.logger.info('生成表格成功')
    sheet3.commit();


    const sheet4 = workbook.addWorksheet('DA7 单个手机呼叫时长集中度分析');
    sheet4.columns = [
      { key: 'month', header: '月份' } as any,
      { key: 't10', header: '(0-10】min' } as any,
      { key: 't20', header: '(10-20】' } as any,
      { key: 't30', header: '(20-30】' } as any,
      { key: 't40', header: '(30-40】' } as any,
      { key: 't50', header: '(40-50】' } as any,
      { key: 't60', header: '(50-60】' } as any,
      { key: 'tmax', header: '>60' } as any,
      { key: 'tno', header: '无效' } as any,
    ];
    let monList = Object.keys(reportCon);
    monList = monList.sort((a, b) => {
      return a > b ? 1: -1
    });
    monList.forEach((month) => {
      const one = reportLen[month];
      const doc = {
        month,
        ...one,
      };
      ['t10','t20', 't30', 't40', 't50', 't60', 'tmax', 'tno'].forEach((type) => {
        if (!doc[type]) {
          doc[type] = 0;
        }
      })
      sheet4.addRow(doc);
    });
    sheet4.commit();

    console.time('执行commit');
    await workbook.commit();
    console.timeEnd('执行commit');
    const time2 = new Date().getTime();
    this.ctx.logger.info(`导出耗时：${time2 - time1} ms`);
  }


  public async getAccounts(): Promise<{dataDB: string, account:string}[]> {
    const accounts = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
      .find({product: 'cc'}, {fields: {_id: 1, dataDB:1, account:1}}).toArray();
    this.ctx.logger.info(accounts.length);
    return accounts as any;
  }

  // public async getDataDB(account) {
  //   let db = dbCache[account];
  //   if (!db) {
  //     const doc = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
  //       .findOne({_id: account + '_cc'}, {fields: {dataDB:1}});
  //     if (!doc) {
  //       return null;
  //     }
  //     dbCache[account] = doc.dataDB;
  //     db = doc.dataDB;
  //   }
  //   return db;
  // }

}
